﻿using log4net;
using System.Data;
using System.Data.OracleClient;
using System.Reflection;
using Koala.Pro.CodeGenerator.UI.Common;
using Koala.Pro.CodeGenerator.UI.DatabaseModel.Schema;

namespace Koala.Pro.CodeGenerator.UI.DatabaseModel.Data
{
    internal class Oracle : Database
    {
        private static readonly ILog log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        public override IDictionary<string, DatabaseInfo> ListDatabases()
        {
            IDictionary<string, DatabaseInfo> dictionary = new Dictionary<string, DatabaseInfo>();
            string commandText = $"SELECT USERNAME FROM DBA_USERS WHERE (ACCOUNT_STATUS = 'OPEN') ";
            using OracleConnection oracleConnection = new OracleConnection(base.ConnectString);
            oracleConnection.Open();
            OracleCommand oracleCommand = new OracleCommand(commandText, oracleConnection);
            IDataReader dataReader = oracleCommand.ExecuteReader();
            while (dataReader.Read())
            {
                DatabaseInfo databaseInfo = new DatabaseInfo();
                databaseInfo.Name = new NameElement(dataReader["USERNAME"].ToString());
                dictionary.Add(databaseInfo.Name.Name.ToString(), databaseInfo);
            }
            dataReader.Close();
            return dictionary;
        }

        public override IDictionary<string, TableInfo> ListTables(string database)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            IDictionary<string, TableInfo> dictionary = new Dictionary<string, TableInfo>();
            string commandText = $"SELECT TABLE_NAME FROM DBA_TABLES WHERE (OWNER = '{database}') ORDER BY TABLE_NAME DESC";
            using OracleConnection oracleConnection = new OracleConnection(base.ConnectString);
            oracleConnection.Open();
            OracleCommand oracleCommand = new OracleCommand(commandText, oracleConnection);
            IDataReader dataReader = oracleCommand.ExecuteReader();
            while (dataReader.Read())
            {
                string text = dataReader["TABLE_NAME"].ToString();
                TableInfo tableInfo = new TableInfo();
                tableInfo.Name = new NameElement(text, StringUtil.ToCapit(text.ToLower()));
                if (base.NoSuffixOfTableName != null)
                {
                    string text2 = StringUtil.RemovePrefix(text, base.NoSuffixOfTableName);
                    text2 = StringUtil.ToCapit(text2.ToLower());
                    log.Debug("+++table Alias Name = " + text2);
                    tableInfo.Name.Alias = new CString(text2);
                }
                dictionary.Add(tableInfo.Name.Name.ToString(), tableInfo);
            }
            dataReader.Close();
            return dictionary;
        }

        private DataTable GetReaderSchema(string database, string tableName, OracleConnection connection)
        {
            IDbCommand dbCommand = new OracleCommand();
            dbCommand.CommandText = $"Select * FROM {database}.{tableName}";
            dbCommand.Connection = connection;
            using IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
            return dataReader.GetSchemaTable();
        }

        public override IDictionary<string, ColumnInfo> ListColumns(string database, string tableName)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            if (tableName == null)
            {
                throw new ArgumentNullException("tableName");
            }
            IDictionary<string, ColumnInfo> dictionary = new Dictionary<string, ColumnInfo>();
            OracleConnection oracleConnection = new OracleConnection(base.ConnectString);
            oracleConnection.Open();
            DataTable readerSchema = GetReaderSchema(database, tableName, oracleConnection);
            foreach (DataRow row in readerSchema.Rows)
            {
                ColumnInfo columnInfo = new ColumnInfo();
                string text = row["ColumnName"].ToString();
                string alias = StringUtil.ToCapit(text.ToLower());
                columnInfo.Name = new NameElement(text, alias);
                columnInfo.Ordinal = Convert.ToInt32(row["ColumnOrdinal"].ToString());
                columnInfo.AllowDBNull = (bool)row["AllowDBNull"];
                columnInfo.MaxLength = Convert.ToInt32(row["ColumnSize"].ToString());
                columnInfo.DataTypeId = Convert.ToInt32(row["ProviderType"].ToString());
                columnInfo.DataType = ((OracleType)row["ProviderType"]).ToString();
                columnInfo.FieldPrecise = Convert.ToInt32(row["NumericPrecision"].ToString());
                columnInfo.IsPrimaryKey = (bool)row["IsKey"];
                columnInfo.Unique = (bool)row["IsUnique"];
                string name = row["DataType"].ToString();
                string simplyNetType = GetSimplyNetType(row["DataType"].ToString());
                columnInfo.NetType = new NameElement(name, simplyNetType);
                columnInfo.UIControlType = GetControlType(row["DataType"].ToString());
                dictionary.Add(columnInfo.Name.Name.ToString(), columnInfo);
            }
            SetDescription(dictionary, database, tableName, oracleConnection);
            SetDefaultValue(dictionary, database, tableName, oracleConnection);
            SetPrimaryKeyAndForeignKey(dictionary, database, tableName, oracleConnection);
            oracleConnection.Close();
            return dictionary;
        }

        private void SetDescription(IDictionary<string, ColumnInfo> list, string database, string tableName, OracleConnection connection)
        {
            string text = $"SELECT * FROM ALL_COL_COMMENTS WHERE OWNER = '{database}' AND TABLE_NAME='{tableName}' ";
            log.Debug("Get Description sql:" + text);
            OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(text, connection);
            DataSet dataSet = new DataSet();
            oracleDataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            foreach (ColumnInfo value in list.Values)
            {
                try
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        if (value.Name.Name.ToString() == row["COLUMN_NAME"].ToString())
                        {
                            value.Description = row["COMMENTS"].ToString();
                            log.Debug($"Column {value.Name.Name}'s Description:{value.Description}");
                        }
                    }
                }
                catch (Exception ex)
                {
                    log.Debug(ex.Message);
                    log.Debug(ex.StackTrace);
                }
            }
        }

        private void SetDefaultValue(IDictionary<string, ColumnInfo> list, string database, string tableName, OracleConnection connection)
        {
            string text = $" Select COLUMN_NAME, DATA_DEFAULT from DBA_TAB_COLUMNS \r\n                        WHERE (OWNER = '{database}') AND (TABLE_NAME = '{tableName}') ORDER BY COLUMN_ID";
            log.Debug("Get DefaultValue sql:" + text);
            OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(text, connection);
            DataSet dataSet = new DataSet();
            oracleDataAdapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            foreach (ColumnInfo value in list.Values)
            {
                try
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        if (value.Name.Name.ToString() == row["COLUMN_NAME"].ToString())
                        {
                            value.DefaultValue = row["DATA_DEFAULT"].ToString();
                            log.Debug($"Column {value.Name.Name}'s defaultValue:{value.DefaultValue}");
                        }
                    }
                }
                catch (Exception ex)
                {
                    log.Debug(ex.Message);
                    log.Debug(ex.StackTrace);
                }
            }
        }

        private void SetPrimaryKeyAndForeignKey(IDictionary<string, ColumnInfo> list, string database, string tableName, OracleConnection connection)
        {
            string selectCommandText = $"\r\n                SELECT\r\n                    C.OWNER, C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE\r\n                    ,C.TABLE_NAME, C.R_OWNER, C.R_CONSTRAINT_NAME,C.INDEX_NAME, \r\n                    L.COLUMN_NAME\r\n                FROM ALL_constraints C, ALL_cons_columns  L \r\n                WHERE C.OWNER = '{database}' AND C.CONSTRAINT_NAME = L.CONSTRAINT_NAME";
            DataSet dataSet = new DataSet();
            OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(selectCommandText, connection);
            oracleDataAdapter.Fill(dataSet);
            if (dataSet.Tables.Count <= 0)
            {
                return;
            }
            DataTable dataTable = dataSet.Tables[0];
            string filterExpression = $"TABLE_NAME = '{tableName}'";
            DataRow[] array = dataTable.Select(filterExpression);
            foreach (KeyValuePair<string, ColumnInfo> item in list)
            {
                DataRow[] array2 = array;
                foreach (DataRow dataRow in array2)
                {
                    if (!(item.Value.Name.Name.ToString() == dataRow["COLUMN_NAME"].ToString()))
                    {
                        continue;
                    }
                    if ("P" == dataRow["CONSTRAINT_TYPE"].ToString())
                    {
                        item.Value.IsPrimaryKey = true;
                        IList<KeyInfo> list2 = new List<KeyInfo>();
                        string filterExpression2 = string.Format("R_CONSTRAINT_NAME = '{0}'", dataRow["CONSTRAINT_NAME"].ToString());
                        DataRow[] array3 = dataTable.Select(filterExpression2);
                        DataRow[] array4 = array3;
                        foreach (DataRow dataRow2 in array4)
                        {
                            string text = dataRow2["TABLE_NAME"].ToString();
                            string name = dataRow2["COLUMN_NAME"].ToString();
                            KeyInfo keyInfo = new KeyInfo();
                            keyInfo.TableName = new NameElement(text);
                            keyInfo.ColumnName = new NameElement(name);
                            if (base.NoSuffixOfTableName != null)
                            {
                                string text2 = StringUtil.RemovePrefix(text, base.NoSuffixOfTableName);
                                log.Debug("+++primary key table Alias Name = " + text2);
                                keyInfo.TableName.Alias = new CString(text2);
                            }
                            log.Debug("KeyInfo:" + keyInfo.ToString());
                            list2.Add(keyInfo);
                        }
                        item.Value.PKBeReferences = list2;
                    }
                    if (!("R" == dataRow["CONSTRAINT_TYPE"].ToString()))
                    {
                        continue;
                    }
                    item.Value.IsForeignKey = true;
                    string filterExpression3 = string.Format("CONSTRAINT_NAME = '{0}'", dataRow["R_CONSTRAINT_NAME"].ToString());
                    DataRow[] array5 = dataTable.Select(filterExpression3);
                    DataRow[] array6 = array5;
                    foreach (DataRow dataRow3 in array6)
                    {
                        KeyInfo keyInfo2 = new KeyInfo();
                        string content = dataRow3["TABLE_NAME"].ToString();
                        dataRow3["COLUMN_NAME"].ToString();
                        keyInfo2.TableName = new NameElement(dataRow3["TABLE_NAME"].ToString());
                        keyInfo2.ColumnName = new NameElement(dataRow3["COLUMN_NAME"].ToString());
                        if (base.NoSuffixOfTableName != null)
                        {
                            string text3 = StringUtil.RemovePrefix(content, base.NoSuffixOfTableName);
                            log.Debug("+++foreign table Alias Name = " + text3);
                            keyInfo2.TableName.Alias = new CString(text3);
                        }
                        item.Value.FKReference = keyInfo2;
                    }
                }
            }
        }

        public override IDictionary<string, ViewInfo> ListViews(string database)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            IDictionary<string, ViewInfo> dictionary = new Dictionary<string, ViewInfo>();
            using OracleConnection oracleConnection = new OracleConnection(base.ConnectString);
            string commandText = $"SELECT OWNER, VIEW_NAME, TEXT FROM ALL_VIEWS WHERE OWNER ='{database}'\r\n                                            ORDER BY VIEW_NAME";
            oracleConnection.Open();
            OracleCommand oracleCommand = new OracleCommand(commandText, oracleConnection);
            using IDataReader dataReader = oracleCommand.ExecuteReader();
            while (dataReader.Read())
            {
                ViewInfo viewInfo = new ViewInfo();
                viewInfo.Name = new NameElement(dataReader["VIEW_NAME"].ToString());
                viewInfo.Text = dataReader["TEXT"].ToString();
                try
                {
                    dictionary.Add(viewInfo.Name.Name.ToString(), viewInfo);
                }
                catch (ArgumentException)
                {
                    log.Debug("The view name is duplicated:" + viewInfo.Name.Name);
                    log.Debug("The view is ignored");
                }
            }
            return dictionary;
        }

        public override IDictionary<string, ProcedureInfo> ListProcedures(string database)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            IDictionary<string, ProcedureInfo> dictionary = new Dictionary<string, ProcedureInfo>();
            using (OracleConnection oracleConnection = new OracleConnection(base.ConnectString))
            {
                string commandText = $"SELECT Distinct NAME FROM DBA_SOURCE \r\n                   WHERE (OWNER = '{database}') AND TYPE='PROCEDURE' ORDER BY NAME";
                oracleConnection.Open();
                OracleCommand oracleCommand = new OracleCommand(commandText, oracleConnection);
                using (IDataReader dataReader = oracleCommand.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        ProcedureInfo procedureInfo = new ProcedureInfo();
                        procedureInfo.Name = new NameElement(dataReader["name"].ToString());
                        try
                        {
                            dictionary.Add(procedureInfo.Name.Name.ToString(), procedureInfo);
                        }
                        catch (ArgumentException)
                        {
                            log.Debug("The procedure name is duplicated:" + procedureInfo.Name.Name);
                            log.Debug("The procedure is ignored");
                        }
                    }
                }
                commandText = $"SELECT NAME, TEXT FROM DBA_SOURCE \r\n                   WHERE (OWNER = '{database}') AND TYPE='PROCEDURE' ORDER BY NAME, LINE ";
                OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(commandText, oracleConnection);
                DataSet dataSet = new DataSet();
                oracleDataAdapter.Fill(dataSet);
                if (dataSet.Tables.Count > 0)
                {
                    foreach (ProcedureInfo value in dictionary.Values)
                    {
                        string filterExpression = string.Format($"NAME = '{value.Name}'", "LINE ASC");
                        DataRow[] array = dataSet.Tables[0].Select(filterExpression);
                        DataRow[] array2 = array;
                        foreach (DataRow dataRow in array2)
                        {
                            if (value.Name.Name.ToString() == dataRow["NAME"].ToString())
                            {
                                value.Text += dataRow["TEXT"].ToString();
                            }
                        }
                    }
                }
            }
            return dictionary;
        }

        public override DataSet ExecuteSql(string database, string query)
        {
            if (database == null)
            {
                throw new ArgumentNullException("database");
            }
            if (query == null)
            {
                throw new ArgumentNullException("query");
            }
            DataSet dataSet = new DataSet();
            using OracleConnection oracleConnection = new OracleConnection(base.ConnectString);
            oracleConnection.Open();
            OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(query, oracleConnection);
            try
            {
                oracleDataAdapter.Fill(dataSet, Guid.NewGuid().ToString());
                return dataSet;
            }
            finally
            {
                ((IDisposable)(object)oracleDataAdapter)?.Dispose();
            }
        }

        public override bool TestConnection()
        {
            bool result = false;
            IDbConnection dbConnection = new OracleConnection(base.ConnectString);
            try
            {
                if (dbConnection.State == ConnectionState.Closed)
                {
                    dbConnection.Open();
                }
                if (dbConnection.State == ConnectionState.Open)
                {
                    result = true;
                }
            }
            catch
            {
            }
            finally
            {
                dbConnection.Close();
            }
            return result;
        }
    }

}
